package com.dashu.core.service;

import com.dashu.base.enums.AtStatus;
import com.dashu.base.enums.device.ParkVdcType;
import com.dashu.base.enums.school.TeacherType;
import com.dashu.base.enums.school.excel.ExcelName;
import com.dashu.base.enums.school.excel.ExcelType;
import com.dashu.base.utils.CopyUtil;
import com.dashu.base.utils.date.TimeUtils;
import com.dashu.base.utils.excel.ExcelUtil;
import com.dashu.core.dto.attendance.*;
import com.dashu.core.dto.company.CompanyClsDTO;
import com.dashu.core.dto.company.CompanyDepartmentDTO;
import com.dashu.core.dto.company.CompanyPersonalDTO;
import com.dashu.core.model.CompanyCls;
import com.dashu.core.model.CompanyPersonal;
import com.dashu.core.model.CompanyTeacher;
import com.dashu.core.modelat.Distinguish;
import com.dashu.core.vo.attendancek.*;
import com.google.common.collect.Lists;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.persistence.Query;
import javax.servlet.http.HttpServletResponse;
import javax.transaction.Transactional;
import java.text.ParseException;
import java.util.*;
import java.util.stream.Collectors;

import static java.util.stream.Collectors.collectingAndThen;
import static java.util.stream.Collectors.toCollection;

@Service
public class AtService extends DataService {

    private String tableName = TimeUtils.getNowTimeDate();
    @Autowired
    CompanyTeacherService companyTeacherService;
    @Autowired
    CompanyPersonalService companyPersonalService;
    @Autowired
    CompanyService companyService;
    @Autowired
    CompanyDepartmentService companyDepartmentService;
    @Autowired
    CompanyClsService companyClsService;

    @Transactional
    public AtTeacherInfoDTO techaeAtInfo(long companyId, long attendanceNum) throws Exception {
        AtTeacherInfoDTO atTecharInfoDTO = new AtTeacherInfoDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id = " + companyId +
                " and iden =  " + " 'TEACHER'" +
                " and attendance_num =  " + attendanceNum +
                " and capture_time between " + TimeUtils.getNowStart() +
                " and " + TimeUtils.getNowEnd());
        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        atTecharInfoDTO.setTeacherCount(companyTeacherService.countCompanyId(companyId));
        atTecharInfoDTO.setTeacherArrive(atTecharInfoDTO.getTeacherCount());

        //统计状态数
        List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTecharInfoDTO.setNormal(normal.size());
        List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTecharInfoDTO.setLate(late.size());
        List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTecharInfoDTO.setLack(lack.size());
        List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTecharInfoDTO.setLeave(leave.size());
        List<Distinguish> notNormal = list.stream().filter(d -> !d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList());
        List<TeacherAtDTO> teacherAtDTOS = Lists.newArrayList();
        notNormal.stream().forEach(nn -> {
            TeacherAtDTO teacherAtDTO = new TeacherAtDTO();
            teacherAtDTO.setCId(nn.getCId());
            teacherAtDTO.setTeacherName(nn.getName());
            teacherAtDTO.setTeacherType(nn.getTeacherType());
            teacherAtDTO.setIcon(nn.getIcon());
            teacherAtDTO.setAtTime(nn.getCaptureTime());
            teacherAtDTO.setStatus(nn.getStatus());
            teacherAtDTOS.add(teacherAtDTO);
        });
        //统计柱状图.
        List<TeacherAtchartDTO> teacherAtchartDTOS = Lists.newArrayList();
        Arrays.asList(TeacherType.values()).stream().forEach(tt -> {

            List<Distinguish> charts = list.stream().filter(ls -> ls.getTeacherType().equals(tt.name())).collect(Collectors.toList());
            TeacherAtchartDTO teacherAtchartDTO = new TeacherAtchartDTO();
            teacherAtchartDTO.setTeacherType(tt.name());
            List<Distinguish> normalChart = charts.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            teacherAtchartDTO.setNormal(normalChart.size());
            List<Distinguish> lateChart = charts.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            teacherAtchartDTO.setLate(lateChart.size());
            List<Distinguish> lackChart = charts.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            teacherAtchartDTO.setLack(lackChart.size());
            List<Distinguish> leaveChart = charts.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            teacherAtchartDTO.setLeave(leaveChart.size());
            teacherAtchartDTOS.add(teacherAtchartDTO);
        });
        atTecharInfoDTO.setTeacherAtchart(teacherAtchartDTOS);

        //异常列表
        List<TeacherAtDTO> teacherAtInfos = teacherAtDTOS.stream().collect(collectingAndThen(toCollection(() ->
                new TreeSet<>(Comparator.comparing(TeacherAtDTO::getCId)
//                        .thenComparing(TeacherAtDTO::getAttendanceNum)
                )), ArrayList::new));
        atTecharInfoDTO.setAtabnormals(teacherAtInfos);
        entityManagerKq.close();
        return atTecharInfoDTO;
    }

    @Transactional
    public AtStudentInfoDTO studentAtInfo(long companyId, long attendanceNum) throws Exception {
        AtStudentInfoDTO atStudentInfoDTO = new AtStudentInfoDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id = " + companyId +
                " and iden =  " + " 'STUDENT'" +
                " and attendance_num =  " + attendanceNum +
                " and capture_time between " + TimeUtils.getNowStart() +
                " and " + TimeUtils.getNowEnd());
        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        List<CompanyPersonal> companyPersonals = companyPersonalService.findByCompanyId(companyId);
        atStudentInfoDTO.setStudentCount(companyPersonals.size());
        atStudentInfoDTO.setStudentArrive(atStudentInfoDTO.getStudentCount());
        List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoDTO.setNormal(normal.size());
        List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoDTO.setLate(late.size());
        List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoDTO.setLack(lack.size());
        List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoDTO.setLeave(leave.size());
        List<Distinguish> notNormal = list.stream().filter(d -> !d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList());
        List<StudentAtDTO> studentAtDTOS = Lists.newArrayList();
        List<CompanyCls> companyCls = companyClsService.findByCompanyId(companyId);
        List<ClsAtDTO> clsAtDTOS = Lists.newArrayList();
        companyCls.stream().forEach(cc -> {
            ClsAtDTO clsAtDTO = new ClsAtDTO();
            CompanyDepartmentDTO companyDepartmentDTO = companyDepartmentService.findByCompanyDepartmentId(cc.getDepartmentId());
            List<Long> companies = companyPersonals.stream().map(CompanyPersonal::getId).collect(Collectors.toList());
            clsAtDTO.setDCName(companyDepartmentDTO.getGradeName() + "/" + cc.getClsName());
            clsAtDTO.setStudentCount(companyPersonals.stream().filter(cp -> cp.getClsId() == cc.getId()).collect(Collectors.toList()).size());
            clsAtDTO.setAtCount(clsAtDTO.getStudentCount());
            clsAtDTO.setNormalCoutnt(normal.stream().filter(nm -> companies.contains(nm.getCId())).collect(Collectors.toList()).size());
            clsAtDTO.setLateCount(late.stream().filter(lt -> companies.contains(lt.getCId())).collect(Collectors.toList()).size());
            clsAtDTO.setLackCount(lack.stream().filter(lc -> companies.contains(lc.getCId())).collect(Collectors.toList()).size());
            clsAtDTO.setLeaveCount(leave.stream().filter(lv -> companies.contains(lv.getCId())).collect(Collectors.toList()).size());
            clsAtDTOS.add(clsAtDTO);
        });
        atStudentInfoDTO.setClsAts(clsAtDTOS);
        notNormal.stream().forEach(nn -> {
            CompanyPersonalDTO companyPersonalDTO = companyPersonalService.findByCompanyPersonalId(nn.getCId());
            CompanyDepartmentDTO companyDepartmentDTO = companyDepartmentService.findByCompanyDepartmentId(companyPersonalDTO.getDepartmentId());
            CompanyClsDTO companyClsDTO = companyClsService.findByCompanyClsId(companyPersonalDTO.getClsId());
            StudentAtDTO studentAtDTO = new StudentAtDTO();
            studentAtDTO.setCId(nn.getCId());
            studentAtDTO.setPersonalName(nn.getName());
            studentAtDTO.setPersonalType(nn.getPersonalType());
            studentAtDTO.setClsName(companyDepartmentDTO.getGradeName() + "/" + companyClsDTO.getClsName());
            studentAtDTO.setDepartmentName(companyClsDTO.getDepartmentName());
            studentAtDTO.setIcon(nn.getIcon());
            studentAtDTO.setAtTime(nn.getCaptureTime());
            studentAtDTO.setStatus(nn.getStatus());
            studentAtDTOS.add(studentAtDTO);
        });
        List<StudentAtDTO> studentAtInfos = studentAtDTOS.stream().collect(collectingAndThen(toCollection(() ->
                new TreeSet<>(Comparator.comparing(StudentAtDTO::getCId)
//                        .thenComparing(TeacherAtDTO::getAttendanceNum)
                )), ArrayList::new));

        atStudentInfoDTO.setAtabnormals(studentAtInfos);
        entityManagerKq.close();
        return atStudentInfoDTO;
    }


    @Transactional
    public TeacherInfoListDTO findTechaeInfo(AtInfoVO atInfoVO) throws Exception {
        TeacherInfoListDTO teacherInfoListDTO = new TeacherInfoListDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                " iden =  " + " 'TEACHER'" +
                " and capture_time between " + TimeUtils.startInfo(atInfoVO.getDate()) +
                " and " + TimeUtils.endInfo(atInfoVO.getDate()));
        if (atInfoVO.getCId() != 0)
            sql.append(" and c_id = " + atInfoVO.getCId());
        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        List<Distinguish> inList = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.IN.name())).collect(Collectors.toList());
        List<Distinguish> outList = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.OUT.name())).collect(Collectors.toList());
        teacherInfoListDTO.setTeacherInfoIn(lationTeacher(inList));
        teacherInfoListDTO.setTeacherInfoOut(lationTeacher(outList));
        entityManagerKq.close();
        return teacherInfoListDTO;
    }

    public List<TeacherInfoDTO> lationTeacher(List<Distinguish> list) {
        List<TeacherInfoDTO> teacherAtDTOS = Lists.newArrayList();
        list.stream().forEach(nn -> {
            TeacherInfoDTO teacherInfoDTO = new TeacherInfoDTO();
            teacherInfoDTO.setAttendanceNum(nn.getAttendanceNum());
            teacherInfoDTO.setCompareResult(nn.getCompareResult());
            teacherInfoDTO.setTemperature(nn.getTemperature());
            teacherInfoDTO.setTeacherName(nn.getName());
            teacherInfoDTO.setTeacherType(nn.getTeacherType());
            teacherInfoDTO.setIcon(nn.getIcon());
            teacherInfoDTO.setImg(nn.getImg());
            teacherInfoDTO.setAtTime(nn.getCaptureTime());
            teacherInfoDTO.setStatus(nn.getStatus());
            teacherAtDTOS.add(teacherInfoDTO);
        });
        return teacherAtDTOS.stream().collect(collectingAndThen(toCollection(() ->
                new TreeSet<>(Comparator.comparing(TeacherInfoDTO::getAttendanceNum))), ArrayList::new));
    }

    @Transactional
    public StudentInfoListDTO findStudentInfo(AtInfoVO atInfoVO) throws Exception {
        StudentInfoListDTO studentInfoListDTO = new StudentInfoListDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                " iden =  " + " 'STUDENT'" +
                " and capture_time between " + TimeUtils.startInfo(atInfoVO.getDate()) +
                " and " + TimeUtils.endInfo(atInfoVO.getDate()));
        if (atInfoVO.getCId() != 0)
            sql.append(" and c_id = " + atInfoVO.getCId());
        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        List<Distinguish> inList = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.IN.name())).collect(Collectors.toList());
        List<Distinguish> outList = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.OUT.name())).collect(Collectors.toList());
        studentInfoListDTO.setStudentInfoIn(lationStudent(inList));
        studentInfoListDTO.setStudentInfoOut(lationStudent(outList));
        entityManagerKq.close();
        return studentInfoListDTO;
    }


    public List<StudentInfoDTO> lationStudent(List<Distinguish> list) {
        List<StudentInfoDTO> studentInfos = Lists.newArrayList();
        list.stream().forEach(nn -> {
            StudentInfoDTO studentInfoDTO = new StudentInfoDTO();
            studentInfoDTO.setAttendanceNum(nn.getAttendanceNum());
            studentInfoDTO.setCompareResult(nn.getCompareResult());
            studentInfoDTO.setTemperature(nn.getTemperature());
            CompanyPersonalDTO companyPersonalDTO = companyPersonalService.findByCompanyPersonalId(nn.getCId());
            studentInfoDTO.setDepartmentName(companyDepartmentService.findByCompanyDepartmentId(companyPersonalDTO.getDepartmentId()).getGradeName());
            studentInfoDTO.setClsName(companyClsService.findByCompanyClsId(companyPersonalDTO.getClsId()).getClsName());
            studentInfoDTO.setNum(companyPersonalDTO.getNum());
            studentInfoDTO.setPersonalName(nn.getName());
            studentInfoDTO.setPersonalType(nn.getTeacherType());
            studentInfoDTO.setIcon(nn.getIcon());
            studentInfoDTO.setImg(nn.getImg());
            studentInfoDTO.setAtTime(nn.getCaptureTime());
            studentInfoDTO.setStatus(nn.getStatus());
            studentInfos.add(studentInfoDTO);
        });
        return studentInfos.stream().collect(collectingAndThen(toCollection(() ->
                new TreeSet<>(Comparator.comparing(StudentInfoDTO::getAttendanceNum))), ArrayList::new));
    }


    @Transactional
    public AtTeacherInfoListDTO findTechaerInfoList(AtTeacherInfoListVO atTeacherInfoListVO) throws Exception {
        AtTeacherInfoListDTO atTeacherInfoListDTO = new AtTeacherInfoListDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id = " + atTeacherInfoListVO.getCompanyId() +
                " and iden =  " + " 'TEACHER'" +
                " and teacher_type =  " + "'" + atTeacherInfoListVO.getTeacherType() + "'" +
                " and capture_time between " + TimeUtils.startInfo(atTeacherInfoListVO.getDate()) +
                " and " + TimeUtils.endInfo(atTeacherInfoListVO.getDate()));
        if (!StringUtils.isEmpty(atTeacherInfoListVO.getTeacherName()))
            sql.append(" and name  like " + "'%" + atTeacherInfoListVO.getTeacherName() + "%'");
        if (atTeacherInfoListVO.getAttendanceNum() != 0)
            sql.append(" and attendance_num =  " + atTeacherInfoListVO.getAttendanceNum());
        if (!StringUtils.isEmpty(atTeacherInfoListVO.getInAndOut()))
            sql.append(" and in_and_out =  " + "'" + atTeacherInfoListVO.getInAndOut() + "'");
        if (!StringUtils.isEmpty(atTeacherInfoListVO.getAtStatus()))
            sql.append(" and status =  " + "'" + atTeacherInfoListVO.getAtStatus() + "'");
        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        atTeacherInfoListDTO.setTeacherCount(companyTeacherService.countCompanyId(atTeacherInfoListVO.getCompanyId()));
        atTeacherInfoListDTO.setTeacherArrive(atTeacherInfoListDTO.getTeacherCount());
        List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTeacherInfoListDTO.setNormal(normal.size());
        List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTeacherInfoListDTO.setLate(late.size());
        List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTeacherInfoListDTO.setLack(lack.size());
        List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atTeacherInfoListDTO.setLeave(leave.size());
//        List<Distinguish> notNormal = list.stream().filter(d -> d.getTeacherType().equals(atTeacherInfoListVO.getTeacherType())).collect(Collectors.toList());
        List<TeacherAtDTO> teacherAtDTOS = Lists.newArrayList();
        list.stream().forEach(nn -> {
            TeacherAtDTO teacherAtDTO = new TeacherAtDTO();
            teacherAtDTO.setCId(nn.getCId());
            teacherAtDTO.setTeacherName(nn.getName());
            teacherAtDTO.setTeacherType(nn.getTeacherType());
            teacherAtDTO.setIcon(nn.getIcon());
            teacherAtDTO.setAtTime(nn.getCaptureTime());
            teacherAtDTO.setStatus(nn.getStatus());
            teacherAtDTO.setCId(nn.getCId());
//            studentInfoDTO.setTemperature(nn.getTemperature());
//            studentInfoDTO.setCompareResult(nn.getCompareResult());
//            studentInfoDTO.setImg(nn.getImg());
            teacherAtDTOS.add(teacherAtDTO);
        });
        List<TeacherAtDTO> teacherAtInfos = teacherAtDTOS.stream().collect(collectingAndThen(toCollection(() ->
                new TreeSet<>(Comparator.comparing(TeacherAtDTO::getCId)
//                        .thenComparing(TeacherAtDTO::getAttendanceNum)
                )), ArrayList::new));
        atTeacherInfoListDTO.setAtabnormals(teacherAtInfos);
        entityManagerKq.close();
        return atTeacherInfoListDTO;
    }


    @Transactional
    public AtStudentInfoListDTO findStudentInfoList(AtStudentInfoListVO atStudentInfoListVO) throws Exception {
        AtStudentInfoListDTO atStudentInfoListDTO = new AtStudentInfoListDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id = " + atStudentInfoListVO.getCompanyId() +
                " and iden =  " + " 'STUDENT'" +
//                " and personal_type =  " + "'" + atStudentInfoListVO.getPersonalType() + "'" +
                " and capture_time between " + TimeUtils.startInfo(atStudentInfoListVO.getDate()) +
                " and " + TimeUtils.endInfo(atStudentInfoListVO.getDate()));
        if (!StringUtils.isEmpty(atStudentInfoListVO.getPersonalName()))
            sql.append(" and name  like " + "'%" + atStudentInfoListVO.getPersonalName() + "%'");
        if (atStudentInfoListVO.getAttendanceNum() != 0)
            sql.append(" and attendance_num =  " + atStudentInfoListVO.getAttendanceNum());
        if (!StringUtils.isEmpty(atStudentInfoListVO.getInAndOut()))
            sql.append(" and in_and_out =  " + "'" + atStudentInfoListVO.getInAndOut() + "'");

        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        atStudentInfoListDTO.setStudentCount(companyTeacherService.countCompanyId(atStudentInfoListVO.getCompanyId()));
        atStudentInfoListDTO.setStudentArrive(atStudentInfoListDTO.getStudentCount());
        List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoListDTO.setNormal(normal.size());
        List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoListDTO.setLate(late.size());
        List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoListDTO.setLack(lack.size());
        List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
        atStudentInfoListDTO.setLeave(leave.size());

        if (!StringUtils.isEmpty(atStudentInfoListVO.getAtStatus()))
            sql.append(" and status =  " + "'" + atStudentInfoListVO.getAtStatus() + "'");
        Query query1 = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list1 = CopyUtil.copyList(query1.getResultList(), Distinguish.class);
//        List<Distinguish> notNormal = list.stream().filter(d -> d.getTeacherType().equals(atTeacherInfoListVO.getTeacherType())).collect(Collectors.toList());
        List<StudentAtDTO> studentInfoDTOS = Lists.newArrayList();
        list1.stream().forEach(nn -> {
            StudentAtDTO studentInfoDTO = new StudentAtDTO();
            studentInfoDTO.setCId(nn.getCId());
            studentInfoDTO.setPersonalName(nn.getName());
            studentInfoDTO.setPersonalType(nn.getPersonalType());
            studentInfoDTO.setIcon(nn.getIcon());
            studentInfoDTO.setAtTime(nn.getCaptureTime());
            studentInfoDTO.setStatus(nn.getStatus());
            studentInfoDTO.setNum(nn.getNum());
//            studentInfoDTO.setTemperature(nn.getTemperature());
//            studentInfoDTO.setCompareResult(nn.getCompareResult());
//            studentInfoDTO.setImg(nn.getImg());
            CompanyPersonalDTO companyPersonalDTO = companyPersonalService.findByCompanyPersonalId(nn.getCId());
            studentInfoDTO.setDepartmentName(companyDepartmentService.findByCompanyDepartmentId(companyPersonalDTO.getDepartmentId()).getGradeName());
            studentInfoDTO.setClsName(companyClsService.findByCompanyClsId(companyPersonalDTO.getClsId()).getClsName());
//            studentInfoDTO.setNum(companyPersonalDTO.getNum());
            studentInfoDTOS.add(studentInfoDTO);
        });
        List<StudentAtDTO> studentAtInfos = studentInfoDTOS.stream().collect(collectingAndThen(toCollection(() ->
                new TreeSet<>(Comparator.comparing(StudentAtDTO::getCId)
//                        .thenComparing(TeacherAtDTO::getAttendanceNum)
                )), ArrayList::new));
        atStudentInfoListDTO.setStudentList(studentAtInfos);
        entityManagerKq.close();
        return atStudentInfoListDTO;
    }

    @Transactional
    public AtTeacherInfoListDTO teacherWeekMonth(AtTeacharInfoWeekMonthVO atTeacharInfoWeekMonthVO) throws Exception {
        AtTeacherInfoListDTO atTeacherInfoListDTO = new AtTeacherInfoListDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id = " + atTeacharInfoWeekMonthVO.getCompanyId() +
                " and iden =  " + " 'TEACHER'");


        if (atTeacharInfoWeekMonthVO.getAttendanceNum() != 0)
            sql.append(" and attendance_num =  " + atTeacharInfoWeekMonthVO.getAttendanceNum());
        if (!StringUtils.isEmpty(atTeacharInfoWeekMonthVO.getInAndOut()))
            sql.append(" and in_and_out =  " + "'" + atTeacharInfoWeekMonthVO.getInAndOut() + "'");
        List<Distinguish> resultList = Lists.newArrayList();
        if (atTeacharInfoWeekMonthVO.getWeekOrMonth() == 1) {
            //周
            sql.append(" and capture_time between " + TimeUtils.getWeekStart() +
                    " and " + TimeUtils.getWeekEnd());
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            atTeacherInfoListDTO.setTeacherCount(companyTeacherService.countCompanyId(atTeacharInfoWeekMonthVO.getCompanyId()));
            atTeacherInfoListDTO.setTeacherArrive(atTeacherInfoListDTO.getTeacherCount());
            List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setNormal(normal.size());
            List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setLate(late.size());
            List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setLack(lack.size());
            List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setLeave(leave.size());
            if (!StringUtils.isEmpty(atTeacharInfoWeekMonthVO.getAtStatus()))
                sql.append(" and status =  " + "'" + atTeacharInfoWeekMonthVO.getAtStatus() + "'");
            TimeUtils.getSevenDate().stream().forEach(day -> {
                try {
                    Query query1 = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
                    List<Distinguish> list1 = CopyUtil.copyList(query1.getResultList(), Distinguish.class);
                    dayList(list1, resultList, day);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
        } else {
            //月
            sql.append(" and capture_time between " + TimeUtils.getMonthStart() +
                    " and " + TimeUtils.getMonthEnd());
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            atTeacherInfoListDTO.setTeacherCount(companyTeacherService.countCompanyId(atTeacharInfoWeekMonthVO.getCompanyId()));
            atTeacherInfoListDTO.setTeacherArrive(atTeacherInfoListDTO.getTeacherCount());
            List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setNormal(normal.size());
            List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setLate(late.size());
            List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setLack(lack.size());
            List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atTeacherInfoListDTO.setLeave(leave.size());
            if (!StringUtils.isEmpty(atTeacharInfoWeekMonthVO.getAtStatus()))
                sql.append(" and status =  " + "'" + atTeacharInfoWeekMonthVO.getAtStatus() + "'");
            TimeUtils.getAllTheDateOftheMonth(new Date()).stream().forEach(month -> {
                try {
                    Query query1 = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
                    List<Distinguish> list1 = CopyUtil.copyList(query1.getResultList(), Distinguish.class);
                    dayList(list1, resultList, month);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
        }
        List<TeacherAtDTO> teacherAtDTOS = Lists.newArrayList();
        resultList.stream().forEach(nn -> {
            TeacherAtDTO teacherAtDTO = new TeacherAtDTO();
            teacherAtDTO.setCId(nn.getCId());
            teacherAtDTO.setTeacherName(nn.getName());
            teacherAtDTO.setTeacherType(nn.getTeacherType());
            teacherAtDTO.setIcon(nn.getIcon());
            teacherAtDTO.setAtTime(nn.getCaptureTime());
            teacherAtDTO.setStatus(nn.getStatus());
            teacherAtDTOS.add(teacherAtDTO);
        });
        atTeacherInfoListDTO.setAtabnormals(teacherAtDTOS);
        entityManagerKq.close();
        return atTeacherInfoListDTO;
    }


    @Transactional
    public List<Distinguish> findInfoList(List<Long> companyList) throws Exception {
        StringBuilder cs = new StringBuilder();
        final long[] size = {companyList.size()};
        companyList.stream().forEach(cl -> {
            size[0]--;
            cs.append(cl);
            if (size[0] != 0)
                cs.append(" , ");

        });
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id in ( " + cs + " )" +
                " and capture_time between " + TimeUtils.getNowStart() +
                " and " + TimeUtils.getNowEnd());
        Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
        List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
        return list;
    }


    @Transactional
    public AtStudentInfoListDTO studentWeekMonth(AtStudentInfoWeekMonthVO atStudentInfoWeekMonthVO) throws Exception {
        AtStudentInfoListDTO atStudentInfoListDTO = new AtStudentInfoListDTO();
        StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                "company_id = " + atStudentInfoWeekMonthVO.getCompanyId() +
                " and iden =  " + " 'STUDENT'");
        if (atStudentInfoWeekMonthVO.getAttendanceNum() != 0)
            sql.append(" and attendance_num =  " + atStudentInfoWeekMonthVO.getAttendanceNum());
        if (!StringUtils.isEmpty(atStudentInfoWeekMonthVO.getInAndOut()))
            sql.append(" and in_and_out =  " + "'" + atStudentInfoWeekMonthVO.getInAndOut() + "'");
        List<Distinguish> resultList = Lists.newArrayList();
        if (atStudentInfoWeekMonthVO.getWeekOrMonth() == 1) {
            //周
            sql.append(" and capture_time between " + TimeUtils.getWeekStart() +
                    " and " + TimeUtils.getWeekEnd());
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            atStudentInfoListDTO.setStudentCount(companyTeacherService.countCompanyId(atStudentInfoWeekMonthVO.getCompanyId()));
            atStudentInfoListDTO.setStudentArrive(atStudentInfoListDTO.getStudentCount());
            List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setNormal(normal.size());
            List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setLate(late.size());
            List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setLack(lack.size());
            List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setLeave(leave.size());
            if (!StringUtils.isEmpty(atStudentInfoWeekMonthVO.getAtStatus()))
                sql.append(" and status =  " + "'" + atStudentInfoWeekMonthVO.getAtStatus() + "'");
            TimeUtils.getSevenDate().stream().forEach(day -> {
                try {

                    Query query1 = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
                    List<Distinguish> list1 = CopyUtil.copyList(query1.getResultList(), Distinguish.class);
                    dayList(list1, resultList, day);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
        } else {
            //月
            sql.append(" and capture_time between " + TimeUtils.getMonthStart() +
                    " and " + TimeUtils.getMonthEnd());
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            atStudentInfoListDTO.setStudentCount(companyTeacherService.countCompanyId(atStudentInfoWeekMonthVO.getCompanyId()));
            atStudentInfoListDTO.setStudentArrive(atStudentInfoListDTO.getStudentCount());
            List<Distinguish> normal = list.stream().filter(d -> d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setNormal(normal.size());
            List<Distinguish> late = list.stream().filter(d -> d.getStatus().equals(AtStatus.LATE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setLate(late.size());
            List<Distinguish> lack = list.stream().filter(d -> d.getStatus().equals(AtStatus.LACK.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setLack(lack.size());
            List<Distinguish> leave = list.stream().filter(d -> d.getStatus().equals(AtStatus.LEAVE.name())).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            atStudentInfoListDTO.setLeave(leave.size());
            if (!StringUtils.isEmpty(atStudentInfoWeekMonthVO.getAtStatus()))
                sql.append(" and status =  " + "'" + atStudentInfoWeekMonthVO.getAtStatus() + "'");
            TimeUtils.getAllTheDateOftheMonth(new Date()).stream().forEach(month -> {
                try {
                    Query query1 = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
                    List<Distinguish> list1 = CopyUtil.copyList(query1.getResultList(), Distinguish.class);
                    dayList(list1, resultList, month);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
        }
        List<StudentAtDTO> studentAtDTOS = Lists.newArrayList();
        resultList.stream().forEach(nn -> {
            StudentAtDTO studentAtDTO = new StudentAtDTO();
            studentAtDTO.setCId(nn.getCId());
            studentAtDTO.setPersonalName(nn.getName());
            studentAtDTO.setPersonalType(nn.getTeacherType());
            studentAtDTO.setIcon(nn.getIcon());
            studentAtDTO.setAtTime(nn.getCaptureTime());
            studentAtDTO.setStatus(nn.getStatus());
            studentAtDTOS.add(studentAtDTO);
        });
        atStudentInfoListDTO.setStudentList(studentAtDTOS);
        entityManagerKq.close();
        return atStudentInfoListDTO;
    }


    public void dayList(List<Distinguish> list, List<Distinguish> resultList, String day) throws ParseException {
        long start = TimeUtils.startInfo(day);
        long end = TimeUtils.endInfo(day);
        resultList.addAll(list.stream().filter(d -> start < d.getCaptureTime() && d.getCaptureTime() < end).collect(Collectors.toList())
                .stream().filter(d -> d.getInAndOut().equals(ParkVdcType.IN.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new)));
        resultList.addAll(list.stream().filter(d -> start < d.getCaptureTime() && d.getCaptureTime() < end).collect(Collectors.toList())
                .stream().filter(d -> d.getInAndOut().equals(ParkVdcType.OUT.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new)));
    }


    public void monthList(List<Distinguish> list, List<Distinguish> resultList, String day) throws ParseException {
        long start = TimeUtils.startInfo(day);
        long end = TimeUtils.endInfo(day);
        resultList.addAll(list.stream().filter(d -> start < d.getCaptureTime() && d.getCaptureTime() < end).collect(Collectors.toList())
                .stream().filter(d -> d.getInAndOut().equals(ParkVdcType.IN.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getAttendanceNum))), ArrayList::new)));
        resultList.addAll(list.stream().filter(d -> start < d.getCaptureTime() && d.getCaptureTime() < end).collect(Collectors.toList())
                .stream().filter(d -> d.getInAndOut().equals(ParkVdcType.OUT.name())).collect(Collectors.toList())
                .stream().collect(collectingAndThen(toCollection(() ->
                        new TreeSet<>(Comparator.comparing(Distinguish::getAttendanceNum))), ArrayList::new)));
    }


    @Transactional
    public void exportTeacherDay(HttpServletResponse response, long companyId, long time) throws Exception {
        List<CompanyTeacher> companyTeachers = companyTeacherService.findByCompanieId(companyId);
        List<Map<String, String>> excelMaps = new ArrayList<>();
        companyTeachers.stream().forEach(ct -> {
            StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                    "c_id = " + ct.getId() +
                    " and iden =  " + " 'TEACHER'" +
                    " and capture_time between " + TimeUtils.getNowStart1(time) +
                    " and " + TimeUtils.getNowEnd1(time));
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            Map<String, String> map = new HashMap<>();
            map.put("TeacherName", ct.getTeacherName());
            map.put("TeacherType", ct.getTeacherType().name);
            map.put("icon", ct.getIcon());
            List<Distinguish> statusList = list.stream().filter(d -> !d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList());
            String statusDay = statusList.size() > 0 ? "异常" : "正常";
            map.put("statusDay", statusDay);
            List<Distinguish> starts = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.IN.name())).collect(Collectors.toList());
            starts.sort(Comparator.comparing(Distinguish::getAttendanceNum).reversed());
            List<Distinguish> ends = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.OUT.name())).collect(Collectors.toList());
            ends.sort(Comparator.comparing(Distinguish::getAttendanceNum).reversed());
            //第一次打卡开始
            List<Distinguish> startd = starts.stream().filter(d -> d.getAttendanceNum() == 1).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish startDistinguish = startd.size() > 0 ? startd.get(0) : new Distinguish();
            String start = startDistinguish.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(startDistinguish.getCaptureTime());
            map.put("start", start);
            String startStatus = startDistinguish.getStatus() == null ? "" : AtStatus.map.get(startDistinguish.getStatus()).name;
            map.put("startStatus", startStatus);
            String startPerature = startDistinguish.getTemperature() == 0 ? "" : String.valueOf(startDistinguish.getTemperature());
            map.put("startPerature", startPerature);
            String startIcon = startDistinguish.getImg() == null ? "" : startDistinguish.getImg();
            map.put("startIcon", startIcon);
            //第一次打卡结束
            List<Distinguish> endd = ends.stream().filter(d -> d.getAttendanceNum() == 1).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish endDistinguish = endd.size() > 0 ? endd.get(0) : new Distinguish();
            String end = endDistinguish.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(endDistinguish.getCaptureTime());
            map.put("end", end);
            String endStatus = endDistinguish.getStatus() == null ? "" : AtStatus.map.get(endDistinguish.getStatus()).name;
            map.put("endStatus", endStatus);
            String endPerature = endDistinguish.getTemperature() == 0 ? "" : String.valueOf(endDistinguish.getTemperature());
            map.put("endPerature", endPerature);
            String endIcon = endDistinguish.getImg() == null ? "" : endDistinguish.getImg();
            map.put("endIcon", endIcon);

            //第二次打卡开始
            List<Distinguish> startd1 = starts.stream().filter(d -> d.getAttendanceNum() == 2).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish startDistinguish1 = startd1.size() > 0 ? startd1.get(0) : new Distinguish();
            String start1 = startDistinguish1.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(startDistinguish1.getCaptureTime());
            map.put("start1", start1);
            String startStatus1 = startDistinguish1.getStatus() == null ? "" : AtStatus.map.get(startDistinguish1.getStatus()).name;
            map.put("startStatus1", startStatus1);
            String startPerature1 = startDistinguish1.getTemperature() == 0 ? "" : String.valueOf(startDistinguish1.getTemperature());
            map.put("startPerature1", startPerature1);
            String startIcon1 = startDistinguish1.getImg() == null ? "" : startDistinguish1.getImg();
            map.put("startIcon1", startIcon1);
            //第二次打卡结束
            List<Distinguish> ends1 = ends.stream().filter(d -> d.getAttendanceNum() == 2).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish endDistinguish1 = ends1.size() > 0 ? ends1.get(0) : new Distinguish();
            String end1 = endDistinguish1.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(endDistinguish1.getCaptureTime());
            map.put("end1", end1);
            String endStatus1 = endDistinguish1.getStatus() == null ? "" : AtStatus.map.get(endDistinguish1.getStatus()).name;
            map.put("endStatus1", endStatus1);
            String endPerature1 = endDistinguish1.getTemperature() == 0 ? "" : String.valueOf(endDistinguish1.getTemperature());
            map.put("endPerature1", endPerature1);
            String endIcon1 = endDistinguish1.getImg() == null ? "" : endDistinguish1.getImg();
            map.put("endIcon1", endIcon1);
            //第三次打卡开始
            List<Distinguish> startd2 = starts.stream().filter(d -> d.getAttendanceNum() == 3).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish startDistinguish2 = startd2.size() > 0 ? startd2.get(0) : new Distinguish();
            String start2 = startDistinguish2.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(startDistinguish2.getCaptureTime());
            map.put("start2", start2);
            String startStatus2 = startDistinguish2.getStatus() == null ? "" : AtStatus.map.get(startDistinguish2.getStatus()).name;
            map.put("startStatus2", startStatus2);
            String startPerature2 = startDistinguish2.getTemperature() == 0 ? "" : String.valueOf(startDistinguish2.getTemperature());
            map.put("startPerature2", startPerature2);
            String startIcon2 = startDistinguish2.getImg() == null ? "" : startDistinguish2.getImg();
            map.put("startIcon2", startIcon2);
            //第三次打卡结束
            List<Distinguish> endd2 = ends.stream().filter(d -> d.getAttendanceNum() == 3).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish endDistinguish2 = endd2.size() > 0 ? endd2.get(0) : new Distinguish();
            String end2 = endDistinguish2.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(endDistinguish2.getCaptureTime());
            map.put("end2", end2);
            String endStatus2 = endDistinguish2.getStatus() == null ? "" : AtStatus.map.get(endDistinguish2.getStatus()).name;
            map.put("endStatus2", endStatus2);
            String endPerature2 = endDistinguish2.getTemperature() == 0 ? "" : String.valueOf(endDistinguish2.getTemperature());
            map.put("endPerature2", endPerature2);
            String endIcon2 = endDistinguish2.getImg() == null ? "" : endDistinguish2.getImg();
            map.put("endIcon2", endIcon2);
            excelMaps.add(map);
        });
        ExcelUtil.exportData(response, ExcelName.ATTENDANCEK_DAY, ExcelType.TEACHER, excelMaps);
        entityManagerKq.close();
    }

    @Transactional
    public void exportStudentDay(HttpServletResponse response, long companyId, long time) throws Exception {
        List<CompanyPersonal> companyPersonals = companyPersonalService.findByCompanyId(companyId);
        List<Map<String, String>> excelMaps = new ArrayList<>();
        companyPersonals.stream().forEach(cp -> {
            StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                    "c_id = " + cp.getId() +
                    " and iden =  " + " 'STUEDNT'" +
                    " and capture_time between " + TimeUtils.getNowStart1(time) +
                    " and " + TimeUtils.getNowEnd1(time));
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            Map<String, String> map = new HashMap<>();
            map.put("personalName", cp.getPersonalName());
            map.put("num", cp.getNum());
            CompanyClsDTO companyClsDTO = companyClsService.findByCompanyClsId(cp.getClsId());
            map.put("cls", companyClsDTO.getClsName());
            map.put("personalType", cp.getPersonalType().name);
            map.put("icon", cp.getIcon());
            List<Distinguish> statusList = list.stream().filter(d -> !d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList());
            String statusDay = statusList.size() > 0 ? "异常" : "正常";
            map.put("statusDay", statusDay);
            List<Distinguish> starts = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.IN.name())).collect(Collectors.toList());
            starts.sort(Comparator.comparing(Distinguish::getAttendanceNum).reversed());
            List<Distinguish> ends = list.stream().filter(d -> d.getInAndOut().equals(ParkVdcType.OUT.name())).collect(Collectors.toList());
            ends.sort(Comparator.comparing(Distinguish::getAttendanceNum).reversed());
            //第一次打卡开始
            List<Distinguish> startd = starts.stream().filter(d -> d.getAttendanceNum() == 1).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish startDistinguish = startd.size() > 0 ? startd.get(0) : new Distinguish();
            String start = startDistinguish.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(startDistinguish.getCaptureTime());
            map.put("start", start);
            String startStatus = startDistinguish.getStatus() == null ? "" : AtStatus.map.get(startDistinguish.getStatus()).name;
            map.put("startStatus", startStatus);
            String startPerature = startDistinguish.getTemperature() == 0 ? "" : String.valueOf(startDistinguish.getTemperature());
            map.put("startPerature", startPerature);
            String startIcon = startDistinguish.getImg() == null ? "" : startDistinguish.getImg();
            map.put("startIcon", startIcon);
            //第一次打卡结束
            List<Distinguish> endd = ends.stream().filter(d -> d.getAttendanceNum() == 1).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish endDistinguish = endd.size() > 0 ? endd.get(0) : new Distinguish();
            String end = endDistinguish.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(endDistinguish.getCaptureTime());
            map.put("end", end);
            String endStatus = endDistinguish.getStatus() == null ? "" : AtStatus.map.get(endDistinguish.getStatus()).name;
            map.put("endStatus", endStatus);
            String endPerature = endDistinguish.getTemperature() == 0 ? "" : String.valueOf(endDistinguish.getTemperature());
            map.put("endPerature", endPerature);
            String endIcon = endDistinguish.getImg() == null ? "" : endDistinguish.getImg();
            map.put("endIcon", endIcon);

            //第二次打卡开始
            List<Distinguish> startd1 = starts.stream().filter(d -> d.getAttendanceNum() == 2).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish startDistinguish1 = startd1.size() > 0 ? startd1.get(0) : new Distinguish();
            String start1 = startDistinguish1.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(startDistinguish1.getCaptureTime());
            map.put("start1", start1);
            String startStatus1 = startDistinguish1.getStatus() == null ? "" : AtStatus.map.get(startDistinguish1.getStatus()).name;
            map.put("startStatus1", startStatus1);
            String startPerature1 = startDistinguish1.getTemperature() == 0 ? "" : String.valueOf(startDistinguish1.getTemperature());
            map.put("startPerature1", startPerature1);
            String startIcon1 = startDistinguish1.getImg() == null ? "" : startDistinguish1.getImg();
            map.put("startIcon1", startIcon1);
            //第二次打卡结束
            List<Distinguish> ends1 = ends.stream().filter(d -> d.getAttendanceNum() == 2).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish endDistinguish1 = ends1.size() > 0 ? ends1.get(0) : new Distinguish();
            String end1 = endDistinguish1.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(endDistinguish1.getCaptureTime());
            map.put("end1", end1);
            String endStatus1 = endDistinguish1.getStatus() == null ? "" : AtStatus.map.get(endDistinguish1.getStatus()).name;
            map.put("endStatus1", endStatus1);
            String endPerature1 = endDistinguish1.getTemperature() == 0 ? "" : String.valueOf(endDistinguish1.getTemperature());
            map.put("endPerature1", endPerature1);
            String endIcon1 = endDistinguish1.getImg() == null ? "" : endDistinguish1.getImg();
            map.put("endIcon1", endIcon1);
            //第三次打卡开始
            List<Distinguish> startd2 = starts.stream().filter(d -> d.getAttendanceNum() == 3).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish startDistinguish2 = startd2.size() > 0 ? startd2.get(0) : new Distinguish();
            String start2 = startDistinguish2.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(startDistinguish2.getCaptureTime());
            map.put("start2", start2);
            String startStatus2 = startDistinguish2.getStatus() == null ? "" : AtStatus.map.get(startDistinguish2.getStatus()).name;
            map.put("startStatus2", startStatus2);
            String startPerature2 = startDistinguish2.getTemperature() == 0 ? "" : String.valueOf(startDistinguish2.getTemperature());
            map.put("startPerature2", startPerature2);
            String startIcon2 = startDistinguish2.getImg() == null ? "" : startDistinguish2.getImg();
            map.put("startIcon2", startIcon2);
            //第三次打卡结束
            List<Distinguish> endd2 = ends.stream().filter(d -> d.getAttendanceNum() == 3).collect(Collectors.toList())
                    .stream().collect(collectingAndThen(toCollection(() ->
                            new TreeSet<>(Comparator.comparing(Distinguish::getCId))), ArrayList::new));
            Distinguish endDistinguish2 = endd2.size() > 0 ? endd2.get(0) : new Distinguish();
            String end2 = endDistinguish2.getCaptureTime() == 0 ? "" : TimeUtils.stampTodatehms(endDistinguish2.getCaptureTime());
            map.put("end2", end2);
            String endStatus2 = endDistinguish2.getStatus() == null ? "" : AtStatus.map.get(endDistinguish2.getStatus()).name;
            map.put("endStatus2", endStatus2);
            String endPerature2 = endDistinguish2.getTemperature() == 0 ? "" : String.valueOf(endDistinguish2.getTemperature());
            map.put("endPerature2", endPerature2);
            String endIcon2 = endDistinguish2.getImg() == null ? "" : endDistinguish2.getImg();
            map.put("endIcon2", endIcon2);
            excelMaps.add(map);
        });
        ExcelUtil.exportData(response, ExcelName.ATTENDANCEK_DAY, ExcelType.STUDENT, excelMaps);
        entityManagerKq.close();
    }

    @Transactional
    public void exportTeacherMonth(HttpServletResponse response, long companyId, long time) throws Exception {
        List<CompanyTeacher> companyTeachers = companyTeacherService.findByCompanieId(companyId);
        List<Distinguish> resultList = Lists.newArrayList();
        List<Map<String, String>> excelMaps = new ArrayList<>();
        companyTeachers.stream().forEach(ct -> {
            StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                    "c_id = " + ct.getId() +
                    " and iden =  " + "'TEACHER'" +
                    " and capture_time between " + TimeUtils.getMonthStart1(time) +
                    " and " + TimeUtils.getMonthEnd1(time));
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            Map<String, String> map = new HashMap<>();
            map.put("TeacherName", ct.getTeacherName());
            map.put("TeacherType", ct.getTeacherType().name);
            map.put("icon", ct.getIcon());
//            List<Distinguish> statusList = list.stream().filter(d -> !d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList());
            TimeUtils.getAllTheDateOftheMonth(new Date()).stream().forEach(month -> {
                try {
                    monthList(list, resultList, month);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
            map.put("normal", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.NORMAL.name()) && rl.getCId() == ct.getId()).collect(Collectors.toList()).size()));
            map.put("absence", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.LACK.name()) && rl.getCId() == ct.getId()).collect(Collectors.toList()).size()));
            map.put("late", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.LATE.name()) && rl.getCId() == ct.getId()).collect(Collectors.toList()).size()));
            map.put("leav", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.LEAVE.name()) && rl.getCId() == ct.getId()).collect(Collectors.toList()).size()));
            final long[] day = {1};

            TimeUtils.getAllTheDateOftheMonth(new Date()).stream().forEach(month -> {
                StringBuilder status = new StringBuilder();
                try {
                    long start = TimeUtils.startInfo(month);
                    long end = TimeUtils.endInfo(month);
                    List<Distinguish> dayList = resultList.stream().filter(d -> start < d.getCaptureTime() && d.getCaptureTime() < end)
                            .collect(Collectors.toList()).stream().filter(dl -> !dl.getStatus().equals(AtStatus.NORMAL.name()) && dl.getCId() == ct.getId()).collect(Collectors.toList());
                    if (dayList.size() > 0) {
                        dayList.stream().forEach(dl -> {
                            status.append("\n 第" + dl.getAttendanceNum() + "次考勤");
                            if (dl.getInAndOut().equals(ParkVdcType.IN.name())) {
                                status.append(" 上学");
                            } else {
                                status.append(" 放学");
                            }
                            status.append(AtStatus.map.get(dl.getStatus()).name);
                        });
                        map.put(String.valueOf(day[0]), status.toString());
                    } else {
                        if (end <= TimeUtils.getNowEnd()) {
                            map.put(String.valueOf(day[0]), "正常");
                        } else {
                            map.put(String.valueOf(day[0]), "");
                        }

                    }
                    day[0]++;
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
            excelMaps.add(map);
        });
        ExcelUtil.exportData(response, ExcelName.ATTENDANCEK_MONTH, ExcelType.TEACHER, excelMaps);
        entityManagerKq.close();
    }


    @Transactional
    public void exportStudentMonth(HttpServletResponse response, long companyId, long time) throws Exception {
        List<CompanyPersonal> companyPersonals = companyPersonalService.findByCompanyId(companyId);
        List<Distinguish> resultList = Lists.newArrayList();
        List<Map<String, String>> excelMaps = new ArrayList<>();
        companyPersonals.stream().forEach(cp -> {
            StringBuilder sql = new StringBuilder("select *  from  " + tableName + " where " +
                    "c_id = " + cp.getId() +
                    " and iden =  " + "'STUDENT'" +
                    " and capture_time between " + TimeUtils.getMonthStart1(time) +
                    " and " + TimeUtils.getMonthEnd1(time));
            Query query = entityManagerKq.createNativeQuery(sql.toString(), Distinguish.class);
            List<Distinguish> list = CopyUtil.copyList(query.getResultList(), Distinguish.class);
            Map<String, String> map = new HashMap<>();
            map.put("personalName", cp.getPersonalName());
            map.put("num", cp.getNum());
            CompanyClsDTO companyClsDTO = companyClsService.findByCompanyClsId(cp.getClsId());
            map.put("cls", companyClsDTO.getClsName());
            map.put("personalType", cp.getPersonalType().name);
            map.put("icon", cp.getIcon());
//            List<Distinguish> statusList = list.stream().filter(d -> !d.getStatus().equals(AtStatus.NORMAL.name())).collect(Collectors.toList());
            TimeUtils.getAllTheDateOftheMonth(new Date()).stream().forEach(month -> {
                try {
                    monthList(list, resultList, month);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
            map.put("normal", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.NORMAL.name()) && rl.getCId() == cp.getId()).collect(Collectors.toList()).size()));
            map.put("absence", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.LACK.name()) && rl.getCId() == cp.getId()).collect(Collectors.toList()).size()));
            map.put("late", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.LATE.name()) && rl.getCId() == cp.getId()).collect(Collectors.toList()).size()));
            map.put("leav", String.valueOf(resultList.stream().filter(rl -> rl.getStatus().equals(AtStatus.LEAVE.name()) && rl.getCId() == cp.getId()).collect(Collectors.toList()).size()));
            final long[] day = {1};
            TimeUtils.getAllTheDateOftheMonth(new Date()).stream().forEach(month -> {
                StringBuilder status = new StringBuilder();
                try {
                    long start = TimeUtils.startInfo(month);
                    long end = TimeUtils.endInfo(month);
                    List<Distinguish> dayList = resultList.stream().filter(d -> start < d.getCaptureTime() && d.getCaptureTime() < end)
                            .collect(Collectors.toList()).stream().filter(dl -> !dl.getStatus().equals(AtStatus.NORMAL.name()) && dl.getCId() == cp.getId()).collect(Collectors.toList());
                    if (dayList.size() > 0) {
                        dayList.stream().forEach(dl -> {
                            status.append("\n 第" + dl.getAttendanceNum() + "次考勤");
                            if (dl.getInAndOut().equals(ParkVdcType.IN.name())) {
                                status.append(" 上学");
                            } else {
                                status.append(" 放学");
                            }
                            status.append(AtStatus.map.get(dl.getStatus()).name);
                        });
                        map.put(String.valueOf(day[0]), status.toString());
                    } else {
                        if (end <= TimeUtils.getNowEnd()) {
                            map.put(String.valueOf(day[0]), "正常");
                        } else {
                            map.put(String.valueOf(day[0]), "");
                        }

                    }
                    day[0]++;
                } catch (ParseException e) {
                    e.printStackTrace();
                }
            });
            excelMaps.add(map);
        });
        ExcelUtil.exportData(response, ExcelName.ATTENDANCEK_MONTH, ExcelType.STUDENT, excelMaps);
        entityManagerKq.close();
    }


    @Transactional
    public List<Object> findpages(String filed, Object o, int start, int pageNumer) {
        String sql = "from " + tableName + " u WHERE u." + filed + "=?";
        System.out.println(sql + "--------page--sql语句-------------");
        List<Object> list = new ArrayList<>();
        try {
            Query query = entityManagerKq.createQuery(sql);
            query.setParameter(1, tableName);
            query.setFirstResult((start - 1) * pageNumer);
            query.setMaxResults(pageNumer);
            list = query.getResultList();
            entityManagerKq.close();
        } catch (Exception e) {
            System.out.println("------------分页错误---------------");
        }

        return list;
    }


}
